import java.lang.*;
import java.sql.*;
import java.time.*;
import java.util.Properties;

enum class SytType(val idx: Int, val tbn: String, val cmm: String) {
    TB1 (1, "tb_user_login",    "登入管理"),
    TB3 (3, "tb_role",          "角色管理"),
    TB4 (4, "tb_user",          "用户管理"),
    TB5 (5, "tb_doc_title",     "职称管理"),
    TB6 (6, "tb_hospital",      "医院管理"),
    TB7 (7, "tb_code",          "集团用户配置"),
    TB8 (8, "tb_doctor",        "专家管理"),
    TB10(10,"tb_doc_account",   "支付帐号"),
    // TB_DIVIDE(9, "专家分成"),
    // TB_ORDER(0, "订单管理"), 
    // TB_PATIENT(2, "患者管理"),
    // TB_SCHEDULE(11, "排期管理"),
    ;
}

// 编译命令:
// kotlinc ./pp.kt -include-runtime -d pp.jar
// 运行命令:
// java -Xbootclasspath/a:./mysql-connector-java-5.1.43.jar -jar pp.jar
fun main() {
    var promp = "Chooose your target database: \n  1. test(xx.xxx.65.41).\n  2. production(xx.xxx.xxx.132). \n";
    promp += "  3. local(127.0.0.1)";
    var conf = getConf(promp);
    var conn = getConn(conf)!!;
    var stmt = getStmt(conn)!!;

    var resultset: ResultSet? = null;

    var autoId: Long = getMaxValue(stmt);
    for(st in SytType.values()) {
        println("START: ----> idx: ${st.idx}, ----> name: ${st.cmm}, ----> table: ${st.tbn}");
        resultset = getQueryResult(stmt, st);

        while(resultset.next()) {
            var id = resultset.getLong("id");
            var deletedInd = resultset.getInt("deleted_ind");
            var inMark = resultset.getInt("mark");
            
            println("id: " + id + ", deletedInd: " + deletedInd + ", makr: " + inMark);
            upsert(conn, id, deletedInd, inMark, autoId, st.idx);
            println("line: 43: done.");
            autoId = autoId + 1;
        }
        println("END: ----> idx: ${st.idx}, ----> name: ${st.cmm}, ----> table: ${st.tbn}");
        println();
    }

    // Thread.sleep(1000);
    resultset!!.close();
    stmt.close();
    conn.close();

    System.exit(0);
}

private fun upsert(conn: Connection, id: Long, deletedInd: Int, inMark: Int, autoId: Long, type: Int) {
    var stmt: Statement? = conn.createStatement();  // 因为upsert执行完之后. 会关连接, 所以此处建立新的.
    var sql: String;
    if(inMark == 1) {
        sql = """ 
UPDATE tb_orgn_sys SET deleted_ind = ${deletedInd} 
WHERE tb_sys_type = ${type} AND tb_id = ${id} AND orgn_id = 1
""";
    }else {
        sql = """ 
INSERT INTO tb_orgn_sys (id,orgn_id,tb_sys_type, tb_id,created_by,created_time, updated_by,updated_time,version, deleted_ind)
VALUES (${autoId},1,${type}, ${id},0,now(), 0,now(),0, ${deletedInd})
""";
    }
    stmt!!.execute(sql);    // 此处执行完, 会自己断开连接.
}

private fun getQueryResult(stmt: Statement, st: SytType): ResultSet {
    var result: ResultSet?;
    var sql: String = """
SELECT t.id, t.deleted_ind,
(CASE WHEN t.id IN 
(SELECT s1.tb_id FROM tb_orgn_sys s1 WHERE s1.tb_sys_type = ${st.idx} AND s1.tb_id = t.id)
THEN 1 ELSE 0 END) mark
FROM ${st.tbn} t 
WHERE t.id NOT IN (SELECT s1.tb_id
FROM tb_orgn_sys s1 WHERE s1.tb_sys_type = ${st.idx} AND s1.deleted_ind = t.deleted_ind AND s1.tb_id = t.id)
    """;
    result = stmt.executeQuery(sql);
    if(stmt.execute(sql)) {
        result = stmt.resultSet
    }
    return result;
}

// 获得关系表里ID的最大值. 
private fun getMaxValue(stmt: Statement): Long {
    var result: ResultSet?;
    var maxVal: Long = 0L;
    var sql: String = """
SELECT MAX(t.id) tempId FROM tb_orgn_sys t 
"""
    result = stmt.executeQuery(sql);
    if(stmt.execute(sql)) {
        result = stmt.resultSet;
        println(result);
    }
    if(result.next()) {
        maxVal = result.getLong("tempId");
    }
    return maxVal + 1;
}


private fun getStmt(conn: Connection): Statement? {
    var stmt: Statement? = conn.createStatement();
    return stmt;
}

private fun getConn(c: MySqlConf): Connection? {
    var url = "jdbc:mysql://"+c.host+":3306/"+c.dtbs+"?user="+c.user+"&password="+c.pswd;
    var conn: Connection? = null;
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection(url)
    }catch (ex: Exception) {
        ex.printStackTrace()
    }
    return conn;
}

private class MySqlConf(val h: String, val u: String, val p: String, val d: String) {
    var host: String = h;
    var user: String = u;
    var pswd: String = p;
    var dtbs: String = d;
}

// 根据用户选项, 得到数据库配置
private fun getConf(promp: String): MySqlConf {
    var conf: MySqlConf;
    while(true) {
        println(promp);
        var ans1 = System.console().readLine("please type in your choise: ");
        if(ans1 == "1") {
            conf = MySqlConf("39.104.65.41", "root", "Mysql-123", "youyida");
            break;
        }else if(ans1 == "2") {
            var code = getVerifyCode()
            println("the verify code: " + code);
            var ans2 = System.console().readLine("please confirm your verify code: ");
            if(ans2 == code) {
                conf = MySqlConf("39.104.187.132", "root", "Mysql-123", "youyida");
                break;
            }
        }else if(ans1 == "3") {
            conf = MySqlConf("127.0.0.1", "root", "kdc", "youyida");
            break;
        }
    }
    return conf;
}

// 生成随机验证码 TODO
private fun getVerifyCode(): String {
    var c = arrayOf("A","B","C","D","E", "F","G","H","I","J", "K","L","M","N","P", "Q","R","S","T","U", "V","W","X","Y","Z");
    return "QWER";
}